package com.ponxu.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.ponxu.db.wrap.RowWrapper;
import com.ponxu.db.wrap.impl.MapRowWrapper;
import com.ponxu.log.Log;
import com.ponxu.log.LogFactory;
import com.ponxu.utils.Lang;

/**
 * 执行 SQL 封装, 注意: 不要在多线程中使用同一个实例
 * 
 * @author xwz
 * 
 */
public class SqlRunner {
	private static final Log LOG = LogFactory.getLog();
	private Connection conn;
	private RowWrapper<?> wrapper;
	private String dbType = "mysql";

	public SqlRunner(Connection conn, RowWrapper<?> wrapper) {
		this.conn = conn;
		this.wrapper = wrapper;
	}

	public SqlRunner(Connection conn) {
		this(conn, MapRowWrapper.getInstance());
	}

	public <T> List<T> select(String sql, RowWrapper<T> wrapper, Object... args) {
		LOG.debug("sql: %s", sql);
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement(sql);
			setParameters(pstmt, args);
			rs = pstmt.executeQuery();
			List<T> list = new ArrayList<T>();
			while (rs.next()) {
				T t = wrapper.wrap(rs);
				list.add(t);
			}
			return list;
		} catch (SQLException e) {
			throw new DBException(e);
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
			} catch (SQLException e) {
			}
			try {
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
			}
		}
	}

	public <T> T selectOne(String sql, RowWrapper<T> wrapper, Object... args) {
		if ("mysql".equalsIgnoreCase(dbType)) {
			sql += " \nLIMIT 0, 1";
		}
		List<T> list = select(sql, wrapper, args);
		if (Lang.isNotEmpty(list)) {
			return list.get(0);
		}
		return null;
	}

	public List<?> select(String sql, Object... args) {
		return select(sql, wrapper, args);
	}

	public Object selectOne(String sql, Object... args) {
		return selectOne(sql, wrapper, args);
	}

	public int execute(String sql, Object... args) {
		LOG.debug("sql: %s", sql);
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql);
			setParameters(pstmt, args);
			return pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new DBException(e);
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
			} catch (SQLException e) {
			}
		}
	}

	/**
	 * 执行插入语句, 返回新id
	 * 
	 * @param sql
	 * @param args
	 * @return
	 */
	public int insert(String sql, Object... args) {
		LOG.debug("sql: %s", sql);
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			setParameters(pstmt, args);
			pstmt.execute();

			ResultSet rs = pstmt.getGeneratedKeys();
			if (rs != null && rs.next()) {
				return rs.getInt(1);
			}

			return 0;
		} catch (SQLException e) {
			throw new DBException(e);
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
			} catch (SQLException e) {
			}
		}
	}

	public void run(String sql) {
		LOG.debug("sql: %s", sql);
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			stmt.execute(sql);
		} catch (SQLException e) {
			throw new DBException(e);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}
		}
	}

	private static void setParameters(PreparedStatement pstmt, Object... args)
			throws SQLException {
		for (int i = 0, n = args.length; i < n; i++) {
			pstmt.setObject(i + 1, args[i]);
		}
	}

	public String getDbType() {
		return dbType;
	}

	public void setDbType(String dbType) {
		this.dbType = dbType;
	}

	public int executeAsync(String sql, Object... args) {
		return 0;
	}
}